package com.test.clyq.daoImpl;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.classic.Session;
import org.springframework.dao.DataAccessException;

import com.test.clyq.dao.CategoryDao;
import com.test.clyq.entity.Category;
import com.test.clyq.entity.Users;
import com.test.dao.hibernate.BaseDaoHibernate;
import com.test.util.FormatExceptionStack;
import com.test.util.ObjectUtils;
import com.test.util.Pagination;
import com.test.util.SessionFactoryUtil;

/**
 * CategoryDaoHibernate. <br>
 * 栏目信息管理.
 * <p>
 * Copyright: Copyright (c) 2010-4-17 下午04:36:39
 * <p>
 * Company: 北京宽连十方数字技术有限公司
 * <p>
 * Author: liuxl@c-platform.com
 * <p>
 * Version: 1.0
 * <p>
 */
public class CategoryDaoHibernate extends BaseDaoHibernate implements CategoryDao {

	/**
	 * 新增栏目信息
	 * 
	 * @param category
	 *            栏目实例
	 * @param user
	 *            用户实例
	 */
	public void addCategory(Category category, Users user) {
		this.log.info("新增栏目信息start");
		try {
			saveOrUpdateItem(category, user);
			this.log.debug("新增栏目信息成功");
			this.log.info("新增栏目信息end");
		}
		catch (DataAccessException e) {
			this.log.error("[Category]addCategory发生错误！\n" + FormatExceptionStack.formatException(e));
		}
	}

	/**
	 * 获得栏目列表（分页使用）
	 * 
	 * @return 栏目信息列表结果集合
	 */
	@SuppressWarnings("unchecked")
	public List<Category> categoryList(Pagination pagination) {
		this.log.info("获得栏目信息列表start");
		String sql = "from Category as category order by category.id  ";
		SessionFactory sessionFactory = getHibernateTemplate().getSessionFactory();
		Session session = sessionFactory.openSession();
		try {
			Query query = session.createQuery("select count(*) " + sql);
			List listCount = query.list();
			if (listCount != null && !listCount.isEmpty()) {
				pagination.setTotalRows(((Integer) listCount.get(0)).intValue()); // 获得记录总数
			} else {
				pagination.setTotalRows(0);
			}

			Query query1 = session.createQuery(sql.toString());
			query1.setFirstResult(pagination.getStartIndex());
			query1.setMaxResults(pagination.getNumPerPage());
			List list = query1.list();
			if (list != null && list.size() > 0) {
				this.log.debug("获得列表数据");
				this.log.info("获得栏目信息列表end");
				return list;
			} else {
				this.log.debug("表中没有数据");
				return null;
			}
		}
		catch (DataAccessException e) {
			this.log.error("[Category]CategoryList发生错误！\n" + FormatExceptionStack.formatException(e));
			return null;
		}
		finally {
			SessionFactoryUtil.isClose(session);
		}
	}

	/**
	 * 根据栏目ID删除栏目
	 * 
	 * @param id
	 *            栏目ID
	 * @param user
	 *            用户实例
	 * @return true or false
	 */
	public boolean delCategoryById(Long id, Users user) {
		this.log.info("根据栏目ID删除栏目start");
		try {
			Category Category = (Category) getItem(Category.class, id);
			if (Category != null) {
				removeItem(Category.class, id, user);
				this.log.debug("删除栏目信息成功");
				this.log.info("根据栏目ID删除栏目end");
				return true;
			} else {
				this.log.debug("删除栏目信息失败");
				return false;
			}
		}
		catch (DataAccessException e) {
			this.log.error("[Category]delCategoryById发生错误！\n" + FormatExceptionStack.formatException(e));
			return false;
		}
	}

	/**
	 * 根据栏目id获得栏目信息
	 * 
	 * @param id
	 *            栏目ID
	 * @return NLCategory 栏目实体
	 */
	public Category getCategoryById(Long id) {
		this.log.info("根据栏目ID获得栏目信息start");
		try {
			Category Category = (Category) getItem(Category.class, id);
			if (Category != null) {
				this.log.info("根据栏目ID获得栏目信息end");
				return Category;
			} else {
				this.log.info("根据栏目ID获得栏目信息end");
				return null;
			}

		}
		catch (DataAccessException e) {
			this.log.error("[Category]getCategoryById发生错误！\n" + FormatExceptionStack.formatException(e));
			return null;
		}
	}

	/**
	 * 获得父栏目下属的所有子栏目列表(包括其本身)
	 * 
	 * @param id
	 *            parentId
	 * @return 栏目信息列表结果集合
	 */
	@SuppressWarnings("unchecked")
	public List<Category> getCategoryListAllByParentId(Long parentId) {
		this.log.info("获得子栏目列表start");
		String sql = "from Category as nlcategory where ( nlcategory.categoryParentId=:parentid or nlcategory.id =:parentid )  order by nlcategory.location desc ";
		SessionFactory sessionFactory = getHibernateTemplate().getSessionFactory();
		Session session = sessionFactory.openSession();
		try {
			Query query = session.createQuery(sql.toString());
			query.setLong("parentid", parentId);

			List list = query.list();
			if (list != null && list.size() > 0) {
				this.log.debug("获得子栏目列表数据");
				this.log.info("获得子栏目列表end");
				return list;
			} else {
				this.log.debug("表中没有数据");
				return null;
			}
		}
		catch (DataAccessException e) {
			this.log.error("[List<Category>]getCategoryListAllByParentId发生错误！\n" + FormatExceptionStack.formatException(e));
			return null;
		}
		finally {
			SessionFactoryUtil.isClose(session);
		}
	}

	/**
	 * 获得父栏目下属的所有子栏目列表
	 * 
	 * @param id
	 *            parentId
	 * @return 栏目信息列表结果集合
	 */
	@SuppressWarnings("unchecked")
	public List<Category> getCategoryListByParentId(Long parentId) {
		this.log.info("获得子栏目列表start");
		String sql = "from Category as category where category.categoryParentId=:parentid order  by category.id  ";
		SessionFactory sessionFactory = getHibernateTemplate().getSessionFactory();
		Session session = sessionFactory.openSession();
		try {
			Query query = session.createQuery(sql.toString());
			query.setLong("parentid", parentId);

			List list = query.list();
			if (list != null && list.size() > 0) {
				this.log.debug("获得子栏目列表数据");
				this.log.info("获得子栏目列表end");
				return list;
			} else {
				this.log.debug("表中没有数据");
				return null;
			}
		}
		catch (DataAccessException e) {
			this.log.error("[List<Category>]getCategoryListByParentId发生错误！\n" + FormatExceptionStack.formatException(e));
			return null;
		}
		finally {
			SessionFactoryUtil.isClose(session);
		}
	}

	/**
	 * 获得父栏目下属的所有子栏目列表
	 * 
	 * @param id
	 *            parentId
	 * @return 栏目信息列表结果集合
	 */
	@SuppressWarnings("unchecked")
	public List<Category> getCategoryListByParentId(Long parentId, String isPublic, String type) {
		this.log.info("获得子栏目列表start");
		String sql = "from Category as category where category.categoryParentId=:parentid ";
		if (isPublic != null && !"".equals(isPublic)) {
			sql = sql + " and isPublic =" + isPublic;
		}
		sql = sql + " order  by category.id  ";
		SessionFactory sessionFactory = getHibernateTemplate().getSessionFactory();
		Session session = sessionFactory.openSession();
		try {
			Query query = session.createQuery(sql.toString());
			query.setLong("parentid", parentId);

			List list = query.list();
			if (list != null && list.size() > 0) {
				this.log.debug("获得子栏目列表数据");
				this.log.info("获得子栏目列表end");
				return list;
			} else {
				this.log.debug("表中没有数据");
				return null;
			}
		}
		catch (DataAccessException e) {
			this.log.error("[List<Category>]getCategoryListByParentId发生错误！\n" + FormatExceptionStack.formatException(e));
			return null;
		}
		finally {
			SessionFactoryUtil.isClose(session);
		}
	}

	/**
	 * 获得栏目信息列表
	 * 
	 * @return 栏目信息列表结果集合
	 */
	@SuppressWarnings("unchecked")
	public List<Category> getCategorys() {
		this.log.info("获得栏目信息列表start");
		SessionFactory sessionFactory = getHibernateTemplate().getSessionFactory();
		Session session = sessionFactory.openSession();
		try {
			Query query = session.createQuery("From Category");
			List list = query.list();
			if (list != null && list.size() > 0) {
				this.log.debug("获得列表数据");
				this.log.info("获得栏目信息列表end");
				return list;
			} else {
				this.log.debug("表中没有数据");
				return null;
			}
		}
		catch (DataAccessException e) {
			this.log.error("[Category]getCategorys发生错误！\n" + FormatExceptionStack.formatException(e));
			return null;
		}
		finally {
			SessionFactoryUtil.isClose(session);
		}
	}

	@Override
	public List<Category> getCategorys(Integer website) {
		this.log.info("获得栏目信息列表start");
		SessionFactory sessionFactory = getHibernateTemplate().getSessionFactory();
		Session session = sessionFactory.openSession();

		try {

			String sql = "from Category as category where category.website=:website";

			/** 专题栏目 */
			if (website == -1) {
				sql = "from Category as category";
			}
			Query query = session.createQuery(sql);
			/** 专题栏目 */
			if (website != -1) {
				query.setInteger("website", website);
			}
			List list = query.list();
			if (list != null && list.size() > 0) {
				this.log.debug("获得列表数据");
				this.log.info("获得栏目信息列表end");
				return list;
			} else {
				this.log.debug("表中没有数据");
				return null;
			}
		}
		catch (DataAccessException e) {
			this.log.error("[Category]getCategorys发生错误！\n" + FormatExceptionStack.formatException(e));
			return null;
		}
		finally {
			SessionFactoryUtil.isClose(session);
		}
	}

	@Override
	public List<Category> getFirstLevelCategoryList(Integer website, Pagination pagination) {
		this.log.info("获得栏目信息列表start");
		String sql = " from Category as category  where category.categoryParentId=0 and category.website=:website order by category.id ";
		SessionFactory sessionFactory = getHibernateTemplate().getSessionFactory();
		Session session = sessionFactory.openSession();
		try {
			Query query = session.createQuery("select count(*)" + sql);
			query.setInteger("website", website);
			List listCount = query.list();
			if (listCount != null && !listCount.isEmpty()) {
				pagination.setTotalRows(((Integer) listCount.get(0)).intValue()); // 获得记录总数
			} else {
				pagination.setTotalRows(0);
			}

			Query query1 = session.createQuery(sql.toString());
			query1.setInteger("website", website);
			query1.setFirstResult(pagination.getStartIndex());
			query1.setMaxResults(pagination.getNumPerPage());
			List list = query1.list();
			if (list != null && list.size() > 0) {
				this.log.debug("获得列表数据");
				this.log.info("获得栏目信息列表end");
				return list;
			} else {
				this.log.debug("表中没有数据");
				return null;
			}
		}
		catch (DataAccessException e) {
			this.log.error("[Category]getFirstLevelCategoryList发生错误！\n" + FormatExceptionStack.formatException(e));
			return null;
		}
		finally {
			SessionFactoryUtil.isClose(session);
		}
	}

	/**
	 * 获得所有一级子栏目列表
	 * 
	 * @return 栏目信息列表结果集合
	 */
	@SuppressWarnings("unchecked")
	public List<Category> getFirstLevelCategoryList(Pagination pagination) {
		this.log.info("获得栏目信息列表start");
		String sql = " from Category as category  where category.categoryParentId=0  order by category.id ";
		SessionFactory sessionFactory = getHibernateTemplate().getSessionFactory();
		Session session = sessionFactory.openSession();
		try {
			Query query = session.createQuery("select count(*)" + sql);
			List listCount = query.list();
			if (listCount != null && !listCount.isEmpty()) {
				pagination.setTotalRows(((Integer) listCount.get(0)).intValue()); // 获得记录总数
			} else {
				pagination.setTotalRows(0);
			}

			Query query1 = session.createQuery(sql.toString());
			query1.setFirstResult(pagination.getStartIndex());
			query1.setMaxResults(pagination.getNumPerPage());
			List list = query1.list();
			if (list != null && list.size() > 0) {
				this.log.debug("获得列表数据");
				this.log.info("获得栏目信息列表end");
				return list;
			} else {
				this.log.debug("表中没有数据");
				return null;
			}
		}
		catch (DataAccessException e) {
			this.log.error("[Category]getFirstLevelCategoryList发生错误！\n" + FormatExceptionStack.formatException(e));
			return null;
		}
		finally {
			SessionFactoryUtil.isClose(session);
		}
	}

	@SuppressWarnings("unchecked")
	public List<Category> getPublicCategorys() {
		SessionFactory sessionFactory = getHibernateTemplate().getSessionFactory();
		Session session = sessionFactory.openSession();
		String hql = "From Category where isPublic = 1";
		try {
			Query query = session.createQuery(hql);
			List<Category> list = query.list();
			if (ObjectUtils.isNull(list)) {
				return new ArrayList<Category>();
			} else {
				return list;
			}
		}
		catch (Exception e) {
			this.log.error("[Category]getPublicCategorys发生错误！\n" + FormatExceptionStack.formatException(e));
			return new ArrayList<Category>();
		}
		finally {
			SessionFactoryUtil.isClose(session);
		}
	}

	/**
	 * 根据 子栏目id 获取 父栏目集合
	 * 
	 * @param cId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Category> getTreeList(Long cId) {
		this.log.info("获得子栏目列表start");
		String sql = "select CATEGORY_ID,CATEGORY_NAME,CATEGORY_HOME_PAGE_URL from T_CATEGORY  tcategory start with tcategory.CATEGORY_ID=:cId connect by prior tcategory.category_parent_id=tcategory.CATEGORY_ID  order by tcategory.CATEGORY_ID ";
		SessionFactory sessionFactory = getHibernateTemplate().getSessionFactory();
		Session session = sessionFactory.openSession();
		try {
			Query query = session.createSQLQuery(sql);
			query.setLong("cId", cId);
			List list = query.list();

			if (list != null && list.size() > 0) {
				List<Category> categoryList = new ArrayList<Category>();

				for (int i = 0; i < list.size(); i++) {
					Category category = new Category();
					Object[] object = (Object[]) list.get(i);

					java.math.BigDecimal mmBigDecimal = (java.math.BigDecimal) object[0];
					int id = mmBigDecimal.intValue();

					category.setId(Long.valueOf(id));
					category.setCategoryName((String) object[1]);
					category.setCategoryHomePageUrl((String) object[2]);
					categoryList.add(category);

				}
				this.log.debug("获得子栏目列表数据");
				this.log.info("获得子栏目列表end");
				return categoryList;
			} else {
				this.log.debug("表中没有数据");
				return null;
			}
		}
		catch (DataAccessException e) {
			this.log.error("[List<Category>]getCategoryListAllByParentId发生错误！\n" + FormatExceptionStack.formatException(e));
			return null;
		}
		finally {
			SessionFactoryUtil.isClose(session);
		}
	}

	/**
	 * 保存或更新栏目信息
	 * 
	 * @param category
	 *            栏目实例
	 * @param user
	 *            用户实例
	 */
	public void saveOrUpdate(Category category, Users user) {
		this.log.info("保存或更新栏目信息start");
		try {
			saveOrUpdateItem(category, user);

			this.log.debug("保存栏目信息成功");
			this.log.info("保存或更新栏目信息end");
		}
		catch (DataAccessException e) {
			this.log.error("[Category]saveOrUpdate发生错误！\n" + FormatExceptionStack.formatException(e));
		}

	}
}